Spreadsheets for Librarians by Bruce White

Author:Bruce White
Language: eng
Format: epub
Publisher: ABC-CLIO

Functions with Multiple Conditions: COUNTIFS

You will by now have some idea of the power of conditional functions, but the questions we have been asking are relatively simple ones, such as “how many residents of West Dulminster are registered with the library?” Or “how many of these books are published by Wiley?” Here’s the beginning of a list of book titles with date, publisher, and price details. You can see the full list here:

http://bit.ly/2XtfOxJ

If you are working along, note the Named ranges and open a new worksheet. To find out the number of titles published by Wiley, we could use the formula

=COUNTIF(publisher, "Wiley")

which returns the answer 2. We have asked for an exact match, but if we had used wildcards

=COUNTIF(publisher, "*Wiley*")

then the answer would be 3 because the formula would also have captured the Wiley-Blackwell title. Our next step is to extend the question to ask “how many books that were published by Wiley cost more than $100?” This question has more than one condition, publisher, and price, so we need to proceed to a new function, COUNTIFS. It takes this form:

=COUNTIFS(range_1,condition_1,range_2,condition_2,range_3,condition_3)

Our question about Wiley books costing more than $100 is expressed as

=COUNTIFS(publisher, "*Wiley*",usd, ">100")

The answer is now 1.

You might have noticed that what we are using here is a logical AND—publisher=Wiley AND price > $100—but that it is expressed rather differently. The S in COUNTIFS signals that an AND statement is to follow. We can now proceed to three conditions by asking how many Cambridge books costing less than $100 and published before 2010 are in the list:

=COUNTIFS(publisher,"*Cambridge*",usd,"<100",pub_date,"<2010")

In Chapter 5 we saw that it was possible to determine whether a value fell between two other values by doing an AND search:

=AND(pub_date>2014,pub_date<2017)

If this formula is placed in a column on the same worksheet as the Named range pub_date, it will return TRUE for rows in which the date is 2015 or 2016 and FALSE for any other date. We can apply this logic then to COUNTIFS:

=COUNTIFS(publisher,"*Cambridge*",usd,"<100",pub_date,"<2010", pub_date, ">1990")

So far we have been looking for specific publishers, but the next step is to list all the publishers in our spreadsheet and find the numbers of titles for each one, plus any other data on prices or dates that we wish to extract. We will do this on a separate worksheet.

Making a List: SUMIFS and AVERAGEIFS

The first step is to create a single list of all the publishers and sort this by date. As some publishers appear more than once, and in real life may appear many times in a worksheet of this kind, we need to copy the list of publishers and remove all duplicates so that each publisher appears only once. To do this, simply go to the publisher range and copy it and paste it into the new worksheet. Go to the Data tab and remove the duplicates; then sort the list of publishers alphabetically.

Once we have a sorted list, the formula goes in the adjacent column and simply references the entries in this list, so that if the list

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.